package ee.greaton.siteManager.DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

import ee.greaton.siteManager.model.Site;

public class SQLiteConnection {

	public static final String KEY_ID = "id";
	public static final String KEY_NAME = "name";
	public static final String KEY_DBURL = "dburl";
	public static final String KEY_DBUSER = "dbuser";
	public static final String KEY_DBPASS = "dbpass";
	public static final String KEY_DBNAME = "dbname";
	public static final String KEY_FTPURL = "ftpurl";
	public static final String KEY_FTPUSER = "ftpuser";
	public static final String KEY_FTPPASS = "ftppass";
	public static final String KEY_ISINSTALLED = "isinstalled";
	public static final String KEY_INSTALLFOLDER = "installfolder";
	public static final String TABLE_SITE = "site";
	public static final String CREATE_SITE = "CREATE TABLE IF NOT EXISTS "
			+ TABLE_SITE + " (" + KEY_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_NAME
			+ " TEXT NOT NULL, " + KEY_DBURL + " TEXT NOT NULL, " + KEY_DBUSER
			+ " TEXT NOT NULL, " + KEY_DBPASS + " TEXT NOT NULL, " + KEY_DBNAME
			+ " TEXT NOT NULL, " + KEY_FTPURL + " TEXT NOT NULL, "
			+ KEY_FTPUSER + " TEXT NOT NULL, " + KEY_FTPPASS
			+ " TEXT NOT NULL, " + KEY_ISINSTALLED
			+ " INTEGER NOT NULL DEFAULT 0, " + KEY_INSTALLFOLDER
			+ " TEXT NOT NULL DEFAULT '-')";

	Connection c = null;

	private static SQLiteConnection instance = null;

	public static SQLiteConnection getInstance() {
		if (instance == null) {
			instance = new SQLiteConnection();
		}
		return instance;
	}

	private SQLiteConnection() {
		try {
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:sites.db");
			createTables();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		System.out.println("Opened database successfully");
	}

	private void createTables() {
		Statement stmt = null;
		try {
			stmt = c.createStatement();
			stmt.executeUpdate(CREATE_SITE);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ArrayList<Site> getAllSites() {
		Statement stmt = null;
		ArrayList<Site> sites = new ArrayList<Site>();
		try {
			stmt = c.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM " + TABLE_SITE
					+ ";");
			while (rs.next()) {
				Site site = new Site();
				site.setId(rs.getInt(KEY_ID));
				site.setName(rs.getString(KEY_NAME));
				site.setDbUrl(rs.getString(KEY_DBURL));
				site.setDbUser(rs.getString(KEY_DBUSER));
				site.setDbPass(rs.getString(KEY_DBPASS));
				site.setDbName(rs.getString(KEY_DBNAME));
				site.setFtpUrl(rs.getString(KEY_FTPURL));
				site.setFtpUser(rs.getString(KEY_FTPUSER));
				site.setFtpPass(rs.getString(KEY_FTPPASS));
				site.setIsInstalled(rs.getInt(KEY_ISINSTALLED));
				site.setInstallFolder(rs.getString(KEY_INSTALLFOLDER));
				sites.add(site);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		Collections.sort(sites, new idComparator());
		return sites;
	}

	public void addSite(Site site) {
		Statement stmt = null;
		try {
			stmt = c.createStatement();
			String sql = "INSERT INTO " + TABLE_SITE + " (" + KEY_NAME + ","
					+ KEY_DBURL + "," + KEY_DBUSER + "," + KEY_DBPASS + ","
					+ KEY_DBNAME + "," + KEY_FTPURL + "," + KEY_FTPUSER + ","
					+ KEY_FTPPASS + ") " + "VALUES ('" + site.getName() + "','"
					+ site.getDbUrl() + "','" + site.getDbUser() + "','"
					+ site.getDbPass() + "','" + site.getDbName() + "','"
					+ site.getFtpUrl() + "','" + site.getFtpUser() + "','"
					+ site.getFtpPass() + "');";
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateInstallFolder(Site site, String newFolder) {
		if (!site.getInstallFolder().equals(newFolder)) {
			Statement stmt = null;
			try {
				stmt = c.createStatement();
				String sql = "UPDATE " + TABLE_SITE + " SET "
						+ KEY_INSTALLFOLDER + " = '" + newFolder + "',"
						+ KEY_ISINSTALLED + " = 0 WHERE " + KEY_ID
						+ " = " + site.getId() + ";";
				stmt.executeUpdate(sql);
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void setInstalled(Site site) {
		if (site.getIsInstalled() != 1) {
			Statement stmt = null;
			try {
				stmt = c.createStatement();
				String sql = "UPDATE " + TABLE_SITE + " SET " + KEY_ISINSTALLED
						+ " = 1 WHERE " + KEY_ID + " = " + site.getId() + ");";
				stmt.executeUpdate(sql);
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void updateSite(Site site) {
		Statement stmt = null;
		try {
			stmt = c.createStatement();
			String sql = "UPDATE " + TABLE_SITE + " SET " + KEY_NAME + " = '"
					+ site.getName() + "'," + KEY_DBURL + " = '"
					+ site.getDbUrl() + "'," + KEY_DBUSER + " = '"
					+ site.getDbUser() + "'," + KEY_DBPASS + " = '"
					+ site.getDbPass() + "'," + KEY_DBNAME + " = '"
					+ site.getDbName() + "'," + KEY_FTPURL + " = '"
					+ site.getFtpUrl() + "'," + KEY_FTPUSER + " = '"
					+ site.getFtpUser() + "'," + KEY_FTPPASS + " = '"
					+ site.getFtpPass() + "' WHERE " + KEY_ID + " = "
					+ site.getId() + ");";
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	class idComparator implements Comparator<Site> {
	    @Override
	    public int compare(Site a, Site b) {
	        return a.getId() < b.getId() ? -1 : a.getId() == b.getId() ? 0 : 1;
	    }
	}
}
